﻿using AuthCenter.Data.Dto;
using AuthCenter.Data.Model._202411;
using AuthCenter.Data.Model._202412;
using AuthCenter.Tools.Password;
using Dapper;
using ExcelConfig;
using SanlieHash;
using SanlieHash.BlockCipher;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AuthCenter.Data.Database
{
    public class AuthDB : IDisposable
    {
        internal IRepo repo;
        public AuthDB()
        {
            var connectstring = AppConfig.Text("auth_center");
            repo = new MysqlRepo(connectstring);
        }

        /// <summary>
        /// 添加应用
        /// </summary>
        /// <param name="name">应用名称</param>
        /// <param name="passway">加密方式</param>
        /// <returns>密码</returns>
        public string MakeApp(string code, string name, string passway, string scheme)
        {
            repo.Open();

            var app = new Application();
            app.AppCode = code;
            app.AppName = name;
            app.AppPassWay = passway;
            var is_exist = repo.Connection.QueryFirstOrDefault<bool>("select true from application where AppCode=@AppCode;", app);
            if (is_exist) return string.Empty;

            app.AppID = Guid.NewGuid();
            app.AppPass = PassGen.MakePassword(32);
            app.CreateTime = DateTime.UtcNow;
            app.Scheme = scheme;

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    repo.Connection.Execute("insert into application (AppID, AppCode, AppName, AppPass, AppPassWay, CreateTime, Scheme) values (@AppID, @AppCode, @AppName, @AppPass, @AppPassWay, @CreateTime, @Scheme);", app, transaction);

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return app.AppPass;
        }
        public AppPassInfo? GetAppPassword(Guid appId)
        {
            repo.Open();

            var password = repo.Connection.QueryFirstOrDefault<AppPassInfo>("select AppPass, AppPassWay from application where AppID=@AppID;", new { AppID = appId });
            return password;
        }
        public string? GetAppScheme(Guid appId)
        {
            repo.Open();

            var scheme = repo.Connection.QueryFirstOrDefault<string>("select Scheme from application where AppID=@AppID;", new { AppID = appId });
            return scheme;
        }
        /// <summary>
        /// 创建用户
        /// </summary>
        /// <param name="user">用户名</param>
        /// <param name="pass">密码</param>
        /// <returns></returns>
        public Guid MakeUser(string user, string pass, string passway)
        {
            repo.Open();

            var account = new Account();
            account.User = user;
            var is_exist = repo.Connection.QueryFirstOrDefault<bool>("select true from account where User=@User;", account);
            if (is_exist) return repo.Connection.QueryFirstOrDefault<Guid>("select UserID from account where User=@User;", account);

            account.UserID = Guid.NewGuid();
            account.Pass = PasswordFactory.Make(pass, PassGen.MakePassword(8), passway);
            account.Passtype = passway;
            account.NextPass = PasswordFactory.Make(pass, PassGen.MakePassword(8), passway);
            account.Status = "正常";
            account.Createtime = DateTime.UtcNow;

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    repo.Connection.Execute("insert into account (UserID, User, Pass, NextPass, Passtype, Createtime, Status) values (@UserID, @User, @Pass, @NextPass, @Passtype, @Createtime, @Status);", account, transaction);

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
            return account.UserID;
        }
        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="UserID"></param>
        public void UpdateUserInfo(UserInfo info)
        {
            repo.Open();
            var is_exist = repo.Connection.QueryFirstOrDefault<bool>("select true from userinfo where UserID=@UserID;", info);

            var pass = AppConfig.Text("敏感字段密钥");
            info.Tel = FastAES.Encrypt(info.Tel, pass);
            info.Phone = FastAES.Encrypt(info.Phone, pass);
            info.Email = FastAES.Encrypt(info.Email, pass);
            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (is_exist)
                    {
                        repo.Connection.Execute("update userinfo set Name=@Name, Roles=@Roles, Gender=@Gender, Remark=Remark, Phone=@Phone, Tel=@Tel, Email=@Email, P1=@P1, P2=@P2, P3=@P3, P4=@P4, P5=@P5, P6=@P6 where UserID=@UserID;", info, transaction);
                    }
                    else
                    {
                        repo.Connection.Execute("insert into userinfo (UserID, Name, Roles, Gender, Phone, Tel, Remark, Email, P1, P2, P3, P4, P5, P6) values (@UserID, @Name, @Roles, @Gender, @Phone, @Tel, @Remark, @Email, @P1, @P2, @P3, @P4, @P5, @P6);", info, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
        }
        public UserInfo? GetUserInfo(Guid userId)
        {
            repo.Open();

            var info = repo.Connection.QueryFirstOrDefault<UserInfo>("select * from userinfo where UserID=@UserID;", new { UserID = userId });
            return info;
        }
        /// <summary>
        /// 链接APP
        /// </summary>
        /// <param name="user">用户名</param>
        /// <param name="app">应用名</param>
        public void LinkApp(Guid userId, string app)
        {
            repo.Open();
            var bind = new AccountBind();
            var appid = repo.Connection.QueryFirstOrDefault<Guid>("select AppID from application where AppCode=@AppCode;", new { AppCode = app });

            if (userId == Guid.Empty) return;
            if (appid == Guid.Empty) return;
            bind.AppID = appid;
            bind.UserID = userId;
            bind.Status = "正常";
            var is_exist = repo.Connection.QueryFirstOrDefault<bool>("select TRUE from account_bind where UserID=@UserID and AppID=@AppID;", bind);
            if (is_exist)
            {
                bind.AppID = repo.Connection.QueryFirstOrDefault<Guid>("select BindID from account_bind where UserID=@UserID and AppID=@AppID and Status<>@Status;", bind);
                if (bind.AppID == Guid.Empty) return;
            }
            else
            {
                bind.BindID = Guid.NewGuid();
                bind.CreateTime = DateTime.UtcNow;
            }

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (is_exist)
                    {
                        repo.Connection.Execute("update account_bind set Status=@Status where BindID=@BindID;", bind, transaction);
                    }
                    else
                    {
                        repo.Connection.Execute("insert into account_bind (BindID, UserID, AppID, CreateTime, Status) values (@BindID, @UserID, @AppID, @CreateTime, @Status);", bind, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
        }
        public bool AppIsAuthUser(Guid userId, Guid appId)
        {
            repo.Open();

            var count = repo.Connection.QueryFirstOrDefault<int>("SELECT count(BindID) FROM auth.account_bind where UserID=@UserID and AppID=@AppID;", new { UserID = userId, AppID = appId });
            return count > 0;
        }
        public void SetSecret(Guid userId, string secret, int no)
        {
            if (no <= 0 || no > 7) return;
            var is_exist = repo.Connection.QueryFirstOrDefault<bool>("select TRUE from account_secretbag where UserID=@UserID;", new { UserID = userId } );

            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (is_exist)
                    {
                        repo.Connection.Execute($"update account_secretbag set AccountSecretBag{no}=@AccountSecretBag where UserID=@UserID;", new {UserID=userId, AccountSecretBag=secret }, transaction);
                    }
                    else
                    {
                        repo.Connection.Execute($"insert into account_secretbag (UserID, AccountSecretBag{no}) values (@UserID, @AccountSecretBag);", new { UserID = userId, AccountSecretBag = secret }, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
        }
        public string GetSecret(Guid userId, int no)
        {
            if (no <= 0 || no > 7) return string.Empty;
            var secret = repo.Connection.QueryFirstOrDefault<string>($"select AccountSecretBag{no} from account_secretbag where UserID=@UserID;", new { UserID = userId });

            return secret ?? string.Empty;
        }
        public void UpdatePermission(Guid userid, Guid appid, string permissionContent)
        {
            repo.Open();
            var permission = new Permission();
            permission.UserID = userid;
            permission.AppID =  appid;

            permission.PermissionID = repo.Connection.QueryFirstOrDefault<Guid>("select PermissionID from permission where UserID=@UserID and AppID=@AppID;", permission);
            
            using (var transaction = repo.Connection.BeginTransaction())
            {
                try
                {
                    if (permission.PermissionID == Guid.Empty)
                    {
                        permission.PermissionID = Guid.NewGuid();
                        repo.Connection.Execute("insert into permission (PermissionID, UserID, AppID, PermissionContent) values (@PermissionID, @UserID, @AppID, @PermissionContent);", permission, transaction);
                    }
                    else
                    {
                        repo.Connection.Execute("update permission set PermissionContent=@PermissionContent where PermissionID=@PermissionID;", permission, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // 发生异常时回滚事务
                    transaction.Rollback();
                    // 处理异常
                    Console.WriteLine(ex.Message);
                }
            }
        }
        public Permission? GetPermission(Guid userid, Guid appid)
        {
            repo.Open();

            var permission =repo.Connection.QueryFirstOrDefault<Permission>("select * from permission where UserID=@UserID and AppID=@AppID;");
            return permission;
        }

        public string? GetUserPass(Guid userID)
        {
            repo.Open();
            var pass = repo.Connection.QueryFirstOrDefault<string>("select Pass from account where UserID=@UserID;", new { UserID = userID });
            return pass;
        }
        public Guid GetUserID(string user)
        {
            repo.Open();
            var info = repo.Connection.QueryFirstOrDefault<Account>("select UserID, Passtype from account where User=@User;", new { User = user });
            if (info == null) return Guid.Empty;
            // 判断是否为别名
            if (info.Passtype.Length == 36)
            {
                return Guid.Parse(info.Passtype);
            }
            return info.UserID;
        }
        public string GetAccountName(Guid userId)
        {
            repo.Open();
            var account = repo.Connection.QueryFirstOrDefault<string>("select User from account where UserID=@UserID;", new {UserID= userId});
            return account ?? string.Empty;
        }
        public string GetUserNextPass(Guid userID)
        {
            repo.Open();
            var pass = repo.Connection.QueryFirstOrDefault<string>("select NextPass from account where UserID=@UserID;", new { UserID = userID });
            return pass ?? "";
        }
        public void UpdateUserPassword(Guid userID, string password, string? limit_passtype = null)
        {
            repo.Open();

            var code1 = PassGen.MakePassword(8);
            var code2 = PassGen.MakePassword(8);

            var passtype = repo.Connection.QueryFirstOrDefault<string>("select Passtype from account where UserID=@UserID;", new { UserId = userID });
            if (limit_passtype != null && !limit_passtype.StartsWith(limit_passtype)) return; //限制密码类型
            if (passtype != null)
            {
                var pwd = PasswordFactory.Make(password, code1, passtype);
                var pwd_next = PasswordFactory.Make(password, code2, passtype);
                using (var transaction = repo.Connection.BeginTransaction())
                {
                    try
                    {
                        repo.Connection.Execute("update account set Pass=@Pass, NextPass=@NextPass where UserID=@UserID;", new { UserID = userID, Pass = pwd, NextPass = pwd_next }, transaction);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        // 发生异常时回滚事务
                        transaction.Rollback();
                        // 处理异常
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }

        public void Dispose()
        {
            repo.Dispose();
        }
    }
}
